#!/usr/bin/python
# -*- coding: utf-8 -*-

import json
import sqlite3

class DbRequestProcessor:
	def __init__(self):
		self.db = sqlite3.connect("../server/db.sqlite")
		
		self.mapping = {}
		self.mapping["ping"] = self._ping
		self.mapping["history"] = self._history
		
	def _ping(self, args):
		return "pong"
	
	def _history(self, args):
		version = args["version"]
		history = args["history"]
		
		cursor = self.db.cursor()
		cursor.execute("SELECT version FROM __webdb_schema")
		serverVersion = cursor.fetchone()[0]
		
		response = {"synced": False}
		
		responseHistory = []
		responseActions = []
		responseMetaActions = []
		message = None
		
		cursor = self.db.cursor()
		if len(history) == 0:
			# history is empty
			if serverVersion > version:
				# TESTED = OK
				# there was only activity on server
				responseActions.extend(self._handleOnlyOnServer(cursor, version, True))
				message = "Synchronized. Server activity applied on client"
			elif serverVersion == version:
				# TESTED = OK
				# everything is synced
				response["synced"] = True
				message = "Synchronized"
			else:
				# Murphy's low
				response["error"] = "ERROR: client acknowledged version is greater then server's. This should not happen."
		else:
			# history is not empty
			if serverVersion == version:
				# there was only activity on client
				responseHistory.extend(self._handleOnlyOnClient(cursor, history))
				message = "Synchronized. Client activity applied on server"
			else:
				# there were activities on client and on server
				cursor.execute("SELECT version, internalId, sql, params, time, metadata, metadataParams, reverse, reverseParams, reverseMetadata, reverseMetaParams from __webdb_history where version > ? order by version", [version])
#				cursor.execute("SELECT version, sql, time, metadata from __webdb_history where version > ? order by version", [version])
				queries = cursor.fetchall()
				response["history"] = []
				
				# filter already applied activities
				filteredHistory, version = self._filterAlreadyApplied(history, queries, responseHistory, version)
				
				# from now we should operate on filteredHistory
				if serverVersion == version:
					# after filtration there are only activities on client side
					responseHistory.extend(self._handleOnlyOnClient(cursor, filteredHistory))
					message = "Synchronized. Client activity applied on server"
				else:
					# conflict resolution
					
					if self._isNonConflictChanges(filteredHistory, queries):
						# activities were on disjoint tables (ignoring metadata tables)
						responseMetaActions2, responseActions2 = self._handleNonConflictChanges(cursor, filteredHistory, queries, version)
						responseMetaActions.extend(responseMetaActions2)
						responseActions.extend(responseActions2)
						message = "Conflict resolved. Changes was automatically merged"
					else:
						# not on disjoint tables
						responseHistory2, responseMetaActions2, responseActions2, message = self._handleConflictChanges(cursor, filteredHistory, queries, responseHistory, version)
						responseHistory.extend(responseHistory2)
						responseMetaActions.extend(responseMetaActions2)
						responseActions.extend(responseActions2)
						
		response["history"] = responseHistory
		response["actions"] = responseActions
		response["metaActions"] = responseMetaActions
		response["message"] = message
		return response

	def __collectStats(self, history, queries):
		stats = {"operations": {"create": [], "drop": [], "insert": [], "update": [], "delete": []}, "tables":set()}
		for i, hEntry in enumerate(history):
			print hEntry
			stats["operations"][self.__getOperation(hEntry["sql"])].append(("history", i))
			stats["tables"].add(self.__getTable(hEntry["sql"]))
			
		for j, qEntry in enumerate(queries):
			print qEntry
			stats["operations"][self.__getOperation(qEntry[2])].append(("queries", j))
			stats["tables"].add(self.__getTable(qEntry[2]))
			
		return stats
	
	def __onOneTable(self, stats):
		return len(stats["tables"]) == 1
	
	def __onlyOneType(self, stats, type):
		for op, nums in stats["operations"].iteritems():
			if (op == type and len(nums) == 0) or (op != type and len(nums) > 0):
				return False
		return True
	
	def __getOperation(self, sql):
		sql = sql.lower()
		for prefix in ["insert", "update", "create", "delete", "drop"]:
			if sql.startswith(prefix):
				return prefix

	def __getTable(self, sql):
		sql = sql.lower()
		for prefix in ["insert into ", "update ", "create table ", "delete from ", "drop table "]:
			if sql.startswith(prefix):
				sql = sql.replace(prefix, "")
				table = sql[:sql.find(" ")]
				return table

	def __listToJson(self, e):
		if isinstance(e, list):
			return json.dumps(e)
		else:
			return e

	def process(self, req):
		print "method =", req["m"], "params =", req["p"]
		return self.mapping[req["m"]](req["p"])

	def _handleOnlyOnServer(self, cursor, version, doCommit):
		# get all acknowledged changes applied after client's last version
		# from database and send to client
		actions = []
		
		try:
			cursor.execute("SELECT version, internalId, sql, params, time, metadata, metadataParams, reverse, reverseParams, reverseMetadata, reverseMetaParams from __webdb_history where version > ? order by version", [version])
			
			for entry in cursor:
				params = json.loads(entry[3]) if entry[3] != None else None
				metadataParams = json.loads(entry[6]) if entry[6] != None else None
				reverseParams = json.loads(entry[8]) if entry[8] != None else None
				reverseMetaParams = json.loads(entry[10]) if entry[10] != None else None
				
				actions.append({"version": entry[0], "internalId": entry[1], "sql": entry[2], "params": params, "time": entry[4], "metadata": entry[5], "metadataParams": metadataParams, "reverse": entry[7], "reverseParams": reverseParams, "reverseMetadata": entry[9], "reverseMetaParams": reverseMetaParams, "override": 1})
			if doCommit:
				self.db.commit()
		except:
			self.db.rollback()
		return actions

	def _handleOnlyOnClient(self, cursor, history):
		responseHistory = []
		try:
			for historyEntry in history:
				entryVersion = historyEntry["version"]
				sql = historyEntry["sql"]
				params = historyEntry["params"]
				metadata = historyEntry["metadata"]
				metadataParams = historyEntry["metadataParams"]
				
				historyQuery, queryParams = self.__convertEntryToQuery(historyEntry)
				
				print historyQuery
				print historyEntry
				cursor.execute(historyQuery, queryParams)
				cursor.execute("update __webdb_schema set version = ?", [entryVersion])
				if params != None:
					cursor.execute(sql, params)
				else:
					cursor.execute(sql)
				if metadata != None:
					if metadataParams != None:
						cursor.execute(metadata, metadataParams)
					else:
						cursor.execute(metadata)
				
				responseHistory.append({"version": entryVersion, "status":1})
			self.db.commit()
		except Exception as ex:
			print ex
			self.db.rollback()
		return responseHistory

	def _filterAlreadyApplied(self, history, queries, responseHistory, version):
		filteredHistory = []
		for i in range(len(history)):
			if i < len(queries) and history[i]["sql"] == queries[i][2] and history[i]["params"] == queries[i][3]:
				responseHistory.append({"version": history[i]["version"], "status":1})
				version += 1
			else:
				# TODO: queries should also be changed
				filteredHistory = history[i:]
				break
		return (filteredHistory, version)

	def _isNonConflictChanges(self, filteredHistory, queries):
		clientTables = set()
		serverTables = set()
		for e in filteredHistory:
			clientTables.add(self.__getTable(e["sql"]))
		print clientTables
		for q in queries:
			serverTables.add(self.__getTable(q[2]))
		print serverTables
		print serverTables.isdisjoint(clientTables)
		
		return serverTables.isdisjoint(clientTables)

	def _handleNonConflictChanges(self, cursor, filteredHistory, queries, version):
		# activities were on disjoint tables (ignoring metadata tables)
		serverSqls = [q[2] for q in queries]
		clientSqls = [he["sql"] for he in filteredHistory]
		
		print serverSqls
		print clientSqls
		
		metaActions = []
		actions = []
		try:
			for historyEntry in filteredHistory:
#				historyEntryVersion = historyEntry["version"]
				sql = historyEntry["sql"]
				params = historyEntry["params"]
				metadata = historyEntry["metadata"]
				metadataParams = historyEntry["metadataParams"]
				
				del historyEntry["version"]
				
				historyQuery, queryParams = self.__convertEntryToQuery(historyEntry)
				print historyQuery, queryParams
				print sql, params, metadata, metadataParams
				# we need to remove version from queryParams because we want auto-generated version
				try:
					cursor.execute(historyQuery, queryParams)
					v = cursor.lastrowid
					cursor.execute("update __webdb_schema set version = ?", [v])
					if params != None:
						cursor.execute(sql, params)
					else:
						cursor.execute(sql)
					if metadata != None:
						if metadataParams != None:
							cursor.execute(metadata, metadataParams)
						else:
							cursor.execute(metadata)
				
				except Exception as e:
					print "operation failed: ", e
					raise e
			
			# META ACTIONS
			metaActions.append({"action": "restoreVersion", "version": version})
			
			# ACTIONS
			actions = self._handleOnlyOnServer(cursor, version, False)
			
			self.db.commit()
		except Exception as e:
			print e
			self.db.rollback()
		
		return (metaActions, actions)

	def _handleConflictChanges(self, cursor, filteredHistory, queries, responseHistory, version):
		# check categories of operations
		print "filteredHistory =", filteredHistory
		print "queries =", queries
		
		metaActions = []
		actions = []
		stats = self.__collectStats(filteredHistory, queries)
		opCount = len(filteredHistory) + len(queries)
		print opCount
		try:
			if self.__onOneTable(stats):
				# operation only on one table
				if self.__onlyOneType(stats, "insert"):
					# sprawdzic czy nie ma primary key ta tej tabeli
					table = self.__getTable(filteredHistory[0]["sql"])
					if self.__definedPrimaryKey(cursor, table):
						# TODO: need to implement some renumeration on pk field
						metaActions.append({"action": "restoreVersion", "version": version})
						actions = self._handleOnlyOnServer(cursor, version, False)
						message = "There was conflict with changes which was applied on server site. Some clients actions was reversed."
					else:
						metaActions, actions = self._handleNonConflictChanges(cursor, filteredHistory, queries, version)
						message = "Status: conflict resolved. Changes was automatically merged"
				elif self.__onlyOneType(stats, "update"):
					metaActions.append({"action": "restoreVersion", "version": version})
					actions = self._handleOnlyOnServer(cursor, version, False)
					message = "There was conflict with changes which was applied on server site. Some clients actions was reversed."
				elif self.__onlyOneType(stats, "delete"):
					metaActions.append({"action": "restoreVersion", "version": version})
					actions = self._handleOnlyOnServer(cursor, version, False)
					message = "There was conflict with changes which was applied on server site. Some clients actions was reversed."
				elif self.__onlyOneType(stats, "create"):
					# create from client should be rejected
					metaActions.append({"action": "restoreVersion", "version": version})
					actions = self._handleOnlyOnServer(cursor, version, False)
					message = "There was conflict with changes which was applied on server site. Some clients actions was reversed."
				# drop look the same!! and should be filtered
				elif self.__onlyIUD(stats):
					# TODO: implement
					filteredHistory = self.__filterUpdates(filteredHistory, queries)
					# TODO: implement
					filteredHistory, queries = self.__transformDeletes(filteredHistory, queries)
					metaActions, actions = self._handleNonConflictChanges(cursor, filteredHistory, queries, version)
					message = "Status: conflict resolved. Changes was automatically merged"
				else:
					metaActions.append({"action": "restoreVersion", "version": version})
					actions = self._handleOnlyOnServer(cursor, version, False)
					message = "There was conflict with changes which was applied on server site. Some clients actions was reversed."
			else:
				# default action if everything failed
				# META ACTIONS
				metaActions.append({"action": "restoreVersion", "version": version})
				actions = self._handleOnlyOnServer(cursor, version, False)
				message = "There was conflict with changes which was applied on server site. Some clients actions was reversed."
			
			self.db.commit()
		except Exception as e:
			print e
			self.db.rollback()
			
		return (responseHistory, metaActions, actions, message)

	def __convertEntryToQuery(self, historyEntry):
		filteredEntry = dict((k, v) for k, v in historyEntry.items() if v != None)
		filteredEntry["status"] = 1
		historyQuery = "INSERT INTO __webdb_history(" + ", ".join(filteredEntry.keys()) + ") VALUES(:" + ", :".join(filteredEntry.keys()) + ")"
		queryParams = dict((k, self.__listToJson(e)) for k, e in filteredEntry.items())
		return (historyQuery, queryParams)
	
	def __restoreVersion(self, cursor, version):
		cursor.execute("SELECT version, reverse, reverseParams, reverseMetadata, reverseMetaParams from __webdb_history where version > ? order by version desc", [version])
		for row in cursor:
			dbVersion = row["version"]
			dbReverse = row["reverse"]
			dbReverseParams = row["dbReverseParams"]
			if dbReverseParams != None:
				dbReverseParams = json.loads(dbReverseParams)
			dbReverseMetadata = row["dbReverseMetadata"]
			dbReverseMetaParams = row["dbReverseMetaParams"]
			if dbReverseMetaParams != None:
				dbReverseMetaParams = json.loads(dbReverseMetaParams)
			
			cursor.execute("DELETE FROM __webdb_history where version = ?", [dbVersion])
			cursor.execute("UPDATE sqlite_sequence SET seq = ? WHERE name = '__webdb_history'", [dbVersion - 1])
			cursor.execute("UPDATE __webdb_schema SET version = ?", [dbVersion - 1])
			
			if dbReverseParams != None:
				cursor.execute(dbReverse, dbReverseParams)
			else:
				cursor.execute(dbReverse)
			
			if dbReverseMetadata != None:
				if dbReverseMetaParams != None:
					cursor.execute(dbReverseMetadata, dbReverseMetaParams)
				else:
					cursor.execute(dbReverseMetadata)
	
	def __definedPrimaryKey(self, cursor, table):
		cursor.execute("SELECT primaryKey FROM __webdb_tablelist where tablename = ?", table)
		return cursor.fetchone()[0]
	
	def __onlyIUD(self, stats):
		op = stats["operations"]
		return len(op["create"]) == 0 and len(op["drop"]) == 0
	
	def __filterUpdates(self, filteredHistory, queries):
		return filteredHistory
	
	def __transformDeletes(self, filteredHistory, queries):
		return (filteredHistory, queries)
	
if __name__ == '__main__':
	import sys
	import os
	import server
	os.chdir(os.path.join(os.path.dirname(__file__), "../client"))
	if len(sys.argv) > 1:
		port = int(sys.argv[1])
		server.main(port)
	else:
		server.main()
